package jsyf.oa.dao.system;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.com.constant.WechatConstant;
import jsyf.com.util.DateUtils;
import jsyf.oa.entity.system.WechatShareEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * Created by WHH-PC on 2018/1/12.
 */
@Repository
public class WechatShareDao extends BaseDao {

    public WechatShareDao() {
        super.iBaseEntity = new WechatShareEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WechatShareEntity.class);
    }

    public WechatShareEntity getById(String id) {
        return (WechatShareEntity) super.getIBaseEntityById(id);
    }

    /**
     * 获取微信分享链接
     *
     * @return
     */
    public List<JSONObject> getWechatShareList(String shareDate, String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.ID as id,a.COMPANY_ID as companyId,a.SHARE_DATE shareDate,a.`NAME` as `name`,  ");
        sb.append("a.LINK_URL as linkUrl,a.IMAGE_URL as imageUrl,a.FORWARD_NUMBER as forwardNumber, ");
        sb.append("a.CREATE_USER_ID as createUserId,a.CREATE_TIME as createTime,b.`NAME` as createUserName, ");
        sb.append("a.REMARK as remark ");
        sb.append("from T_SYS_WECHAT_SHARE as a ");
        sb.append("left join V_SYS_USER as b on a.CREATE_USER_ID=b.ID ");
        sb.append("WHERE a.SHARE_DATE='" + shareDate + "' AND a.COMPANY_ID='" + companyId + "'");
        return queryForJsonList(sb.toString());
    }

    /**
     * 微信分享汇总数
     *
     * @param roleName
     * @param month
     * @param today
     * @param departId
     * @param companyId
     * @param userId
     * @return
     */
    public JSONObject getWechatShareTotal(String roleName, String month, String today, String departId, String companyId, String userId) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbPeople = new StringBuffer();
        Integer peopleNum = 1;//区域经理、大区经理人数
        if (roleName.equals("大区经理")) {
            //某个大区的人
            //sbPeople.append("SELECT id as userId from v_sys_user where departId='" + departId + "' and jobState!=1 AND delFlag=0 ");
        } else if (roleName.equals("区域支持")) {
            //区域支持所负责区域的人
            sbPeople.append("SELECT d.id as userId from (  ");
            sbPeople.append("SELECT a.REGION_ID as regionId,REPLACE(b.`name`, '大区', '') as regionName,b.parent_id as parentId from t_sys_user_2_region as a  ");
            sbPeople.append("left join t_sys_region as b on a.REGION_ID = b.id  ");
            sbPeople.append("where a.user_id='" + userId + "' ");
            sbPeople.append("order by b.parent_id ASC,b.sort_no asc) as a  ");
            sbPeople.append("LEFT JOIN t_sys_region as b on a.parentId=b.id  ");
            sbPeople.append("LEFT JOIN v_region_manager as c on a.regionId=c.regionid  ");
            sbPeople.append("LEFT JOIN v_sys_user as d on c.userid=d.id  AND d.jobState!=1  ");
            sbPeople.append("WHERE c.userid is not null and d.roleName in ('区域经理','大区经理') ");
            sbPeople.append("order by parent_id asc  ");
        } else if (roleName.equals("销售管理经理") || roleName.equals("总经理") || roleName.equals("销售总监")) {
            //某个公司人
            sbPeople.append("SELECT id as userId from v_sys_user where companyId='" + companyId + "'  and jobState!=1 ");
            sbPeople.append("AND roleName in ('区域经理','大区经理') ");
        }
        String userIds = "";
        if (!sbPeople.toString().equals("")) {
            List<JSONObject> userList = queryForJsonList(sbPeople.toString());
            if (userList.size() > 0) {
                peopleNum = userList.size();
            }
            for (Integer i = 0; i < userList.size(); i++) {
                userIds += ",'" + userList.get(i).get("userId").toString() + "'";
            }
        }
        userIds = userIds.length() > 1 ? userIds.substring(1, userIds.length()) : "";
        sb.append("SELECT ");
        Date currentTime = new Date();

        if(DateUtils.isInDate(currentTime,WechatConstant.begiinFormat , WechatConstant.nightStart )){
            sb.append("0 AS todayForward, ");
        }else if (DateUtils.isInDate(currentTime,WechatConstant.nightStart , WechatConstant.closeFormat )){
            sb.append("IFNULL((SELECT COUNT(*)*" + peopleNum + " FROM t_sys_wechat_share WHERE  share_date='" + today + "' AND company_id='" + companyId + "' ");
            sb.append("),0) as todayForward, ");
        }
        sb.append("IFNULL((SELECT count(*) FROM t_sys_wechat_share_detail WHERE  ");
        sb.append("NOW() >= CREATE_TIME AND DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(CREATE_TIME,'%Y-%m-%d') AND del_flag=0 ");
        //sb.append("LEFT(create_time,10)='" + today + "' AND del_flag=0 ");
        if (roleName.equals("区域经理") || roleName.equals("大区经理")) {
            sb.append("AND USER_ID='" + userId + "' ");
        } else if (!userIds.equals("")) {
            sb.append(" AND USER_ID IN (" + userIds + ")");
        }else {
            sb.append("AND USER_ID='" + userId + "' ");
        }
        sb.append("),0)  as todayFinishForward, ");
        sb.append("IFNULL((SELECT SUM(FORWARD_NUMBER)*" + peopleNum + " FROM t_sys_wechat_share WHERE LEFT(share_date,7)='" + month + "'  AND company_id='" + companyId + "'  ");
        sb.append("),0)  as monthForward, ");
        sb.append("IFNULL((SELECT count(*) FROM t_sys_wechat_share_detail WHERE ");
        sb.append("LEFT(create_time,7)='" + month + "'  AND del_flag=0 ");
        if (roleName.equals("区域经理") || roleName.equals("大区经理")) {
            sb.append("AND USER_ID='" + userId + "' ");
        } else if (!userIds.equals("")) {
            sb.append(" AND USER_ID IN (" + userIds + ")");
        }else{
            sb.append("AND USER_ID='" + userId + "' ");
        }
        sb.append("),0) as monthFinishForward ");
        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 获取某人某天或某月的转发汇总
     *
     * @return
     */
    public JSONObject getShareByUserId(String userId, String isToday, String shareMonth) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT ");

        //sb.append("IFNULL((SELECT SUM(FORWARD_NUMBER) from t_sys_wechat_share where ");
        if (isToday.equals("1")) {
            if (DateUtils.isInDate(new Date(),WechatConstant.begiinFormat,WechatConstant.nightStart)){
                sb.append("IFNULL((SELECT COUNT(*)*0 from t_sys_wechat_share where ");
            }
            else if (DateUtils.isInDate(new Date(),WechatConstant.nightStart,WechatConstant.closeFormat)){
                sb.append("IFNULL((SELECT COUNT(*)*1 from t_sys_wechat_share where ");
            }
            sb.append(" SHARE_DATE=date_format(now(),'%Y-%m-%d') ");
        } else {
            sb.append("IFNULL((SELECT SUM(FORWARD_NUMBER) FROM t_sys_wechat_share where");
            sb.append(" LEFT(SHARE_DATE,7)='" + shareMonth + "' ");
        }
        sb.append(" AND COMPANY_ID IN ( ");
        sb.append("SELECT companyId from v_sys_user where id='" + userId + "' AND main = 1 )),0) as forward, ");
        sb.append("IFNULL((SELECT count(*) from t_sys_wechat_share_detail where ");
        if (isToday.equals("1")) {
            sb.append("NOW() >= CREATE_TIME AND DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')");
            //sb.append(" left(create_time,10)=date_format(now(),'%Y-%m-%d')");
        } else {
            sb.append(" left(create_time,7)='" + shareMonth + "'");
        }
        sb.append("AND USER_ID='" + userId + "' AND del_flag=0),0) as finishForward ");
        if (isToday.equals("1")) {
            sb.append(", IFNULL((SELECT CONTACTS from t_wechat_friends_contacts where ");
            sb.append("USER_ID='"+userId+"' AND LEFT(CREATE_TIME,10) = LEFT(NOW(),10)),0) AS contacts" );
        }


        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 获取集团下所有公司
     *
     * @return
     */
    public List<JSONObject> getCompanyList() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT * from v_sys_company WHERE companyParentId!='0' and companyDelFlag='0' AND companyName not like '%大客户%' ");
        return queryForJsonList(sb.toString());
    }

    /**
     * 获取某个公司某天或某月的转发汇总
     *
     * @return
     */
    public JSONObject getShareByCompanyId(String companyId, String isToday, String shareMonth) {
        StringBuffer sb = new StringBuffer();
        StringBuffer sbPeople = new StringBuffer();
        Integer peopleNum = 1;
        //某个公司人
        sbPeople.append("SELECT id as userId from v_sys_user where companyId='" + companyId + "'  and jobState!=1 ");
        sbPeople.append("AND roleName in ('区域经理','大区经理') ");
        if (!sbPeople.toString().equals("")) {
            List<JSONObject> userList = queryForJsonList(sbPeople.toString());
            if (userList.size() > 0) {
                peopleNum = userList.size();
            }
        }
        sb.append("SELECT ");

        if (isToday.equals("1")) {
            if (DateUtils.isInDate(new Date(),WechatConstant.begiinFormat,WechatConstant.nightStart)){
                sb.append("IFNULL((SELECT COUNT(*)*" + 0 + " from t_sys_wechat_share where ");
          }else if (DateUtils.isInDate(new Date(),WechatConstant.nightStart,WechatConstant.closeFormat)){
                sb.append("IFNULL((SELECT COUNT(*)*1*" + peopleNum + " from t_sys_wechat_share where ");
            }
            sb.append(" SHARE_DATE=date_format(now(),'%Y-%m-%d') ");
        } else {
            sb.append(" IFNULL((SELECT SUM(FORWARD_NUMBER)*" + peopleNum + " FROM t_sys_wechat_share WHERE");
            sb.append(" LEFT(SHARE_DATE,7)='" + shareMonth + "' ");
        }
        sb.append(" AND COMPANY_ID='" + companyId + "' ");
        sb.append("),0) as forward, ");
        sb.append("IFNULL((SELECT count(*) from t_sys_wechat_share_detail where ");
        if (isToday.equals("1")) {
             sb.append("NOW() >= CREATE_TIME AND DATE_FORMAT(NOW(),'%Y-%m-%d')=DATE_FORMAT(CREATE_TIME,'%Y-%m-%d')");
            // sb.append(" left(create_time,10)=date_format(now(),'%Y-%m-%d')");
            /**
             *
             */
        } else {
            sb.append(" left(create_time,7)='" + shareMonth + "'");
        }
        sb.append("AND COMPANY_ID='" + companyId + "' AND del_flag=0),0) as finishForward ");
        return queryForJsonList(sb.toString()).get(0);
    }

    /**
     * 获取公司所有大区及人员
     *
     * @return
     */
    public List<JSONObject> getRegionAndUserListByCompany(String companyId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT a.*,REPLACE(b.`name`, '大区', '') as bigRegionName,c.username as userName,  ");
        sb.append("c.userid as userId,d.roleName,d.jobState from (   ");
        sb.append("select id as regionId,REPLACE(`name`, '大区', '') as regionName,parent_id as parentId ");
        sb.append("from t_sys_region where company_id='" + companyId + "') as a  ");
        sb.append("LEFT JOIN t_sys_region as b on a.parentId=b.id LEFT JOIN v_region_manager as c  ");
        sb.append("on a.regionId=c.regionid LEFT JOIN v_sys_user as d on c.userid=d.id  AND d.jobState!=1 order by parent_id asc  ");
        return queryForJsonList(sb.toString());
    }

}
